class SearchesController < ApplicationController
#  before_filter :admin?
  layout "main"

  def new
    @search= Search.new
  end

  def new2
    render :action=>"new2"
  end

  def result
    @sports = User.find_by_sql("select distinct(sport) as sp from users order by sport")
    @fname = params[:fname]
    @lname = params[:lname]
    @gender = params[:gender]
    @min_age = params[:min_age]
    @max_age = params[:max_age]
    @province = params[:province]
    @education_id = params[:education_id]
    @sport = params[:sport]
    @syllabus_id = params[:syllabus_id]
    @pass_cond = params[:pass_cond]
    
    qwhere = Array.new
    qwhere = qwhere << "role!='C'"
    if !@fname.empty?
      qwhere = qwhere << "fname LIKE('%#{@fname}%')"
    end
    if !@lname.empty?
      qwhere = qwhere << "lname LIKE('%#{@lname}%')"
    end
    if !@gender.empty?
      if @gender!='all'
        qwhere = qwhere << "gender='#{@gender}'"
      end
    end
    if !@min_age.empty?
      date = Date.today
      min_date = Date.parse("#{date.year-@min_age.to_i}-#{date.month}-#{date.day}")
      qwhere = qwhere << "dob<='#{min_date}'"
    end
    if !@max_age.empty?
      date = Date.today
      max_date = Date.parse("#{date.year-@max_age.to_i}-#{date.month}-#{date.day}")
      qwhere = qwhere << "dob>='#{max_date}'"
    end
    if !@province.empty?
      if @province!='all'
        qwhere = qwhere << "province='#{@province}'"
      end
    end
    if !@education_id.empty?
      if @education_id!='0'
        qwhere = qwhere << "education_id>=#{@education_id}"
      end
    end
    user_list = Array.new
    word = ""
    if @syllabus_id!='0'
      word =  " AND s.id=#{params[:syllabus_id].to_i}"
      f_sql = "SELECT DISTINCT(r.user_id) AS user_id FROM users u,syllabuses s,syllabus_openings so,registrations r,registration_courses rc,certificate_course_rules ccr,courses c WHERE c.id=ccr.course_id AND rc.course_id=c.id AND ccr.syllabus_id=s.id AND rc.registration_id=r.id AND s.id=so.syllabus_id AND r.syllabus_opening_id=so.id AND r.user_id=u.id#{word}"
      if params[:pass_cond]=='p'
        f_sql = "SELECT DISTINCT(r.user_id) AS user_id FROM users u,syllabuses s,syllabus_openings so,registrations r,registration_courses rc,certificate_course_rules ccr,courses c WHERE c.id=ccr.course_id AND rc.course_id=c.id AND ccr.syllabus_id=s.id AND rc.registration_id=r.id AND s.id=so.syllabus_id AND r.syllabus_opening_id=so.id AND r.user_id=u.id AND rc.grade<=ccr.minimum_grade#{word}"
      end
      if params[:pass_cond]=='f'
        f_sql = "SELECT DISTINCT(r.user_id) AS user_id FROM users u,syllabuses s,syllabus_openings so,registrations r,registration_courses rc,certificate_course_rules ccr,courses c WHERE c.id=ccr.course_id AND rc.course_id=c.id AND ccr.syllabus_id=s.id AND rc.registration_id=r.id AND s.id=so.syllabus_id AND r.syllabus_opening_id=so.id AND r.user_id=u.id AND (rc.grade IS NULL OR rc.grade>ccr.minimum_grade)#{word}"
      end
      filtered_users = Registration.find_by_sql f_sql
      filtered_users.each do |fu|
        user_list = user_list << fu.user_id
      end
      if user_list.size>0
        qwhere = qwhere << "id IN (#{user_list.uniq.join(',')})"
      else
        qwhere = qwhere << "id IN (0)"
      end
    else
      if params[:pass_cond]!='all'
        if params[:pass_cond]=='p'
          f_sql = "SELECT DISTINCT(r.user_id) AS user_id FROM users u,syllabuses s,syllabus_openings so,registrations r,registration_courses rc,certificate_course_rules ccr,courses c WHERE c.id=ccr.course_id AND rc.course_id=c.id AND ccr.syllabus_id=s.id AND rc.registration_id=r.id AND s.id=so.syllabus_id AND r.syllabus_opening_id=so.id AND r.user_id=u.id AND rc.grade<=ccr.minimum_grade"
        end
        if params[:pass_cond]=='f'
          f_sql = "SELECT DISTINCT(r.user_id) AS user_id FROM users u,syllabuses s,syllabus_openings so,registrations r,registration_courses rc,certificate_course_rules ccr,courses c WHERE c.id=ccr.course_id AND rc.course_id=c.id AND ccr.syllabus_id=s.id AND rc.registration_id=r.id AND s.id=so.syllabus_id AND r.syllabus_opening_id=so.id AND r.user_id=u.id AND (rc.grade IS NULL OR rc.grade>ccr.minimum_grade)"
        end
        filtered_users = Registration.find_by_sql f_sql
        filtered_users.each do |fu|
          user_list = user_list << fu.user_id
        end
        if user_list.size>0
          qwhere = qwhere << "id IN (#{user_list.uniq.join(',')})"
        else
          qwhere = qwhere << "id IN (0)"
        end
      end
    end

    sql = ""
    if qwhere.size>0
      sql = "select * from users where #{qwhere.join(' AND ')} order by fname"
    else
      sql = "select * from users order by fname"
    end
    @users = User.find_by_sql sql
    render :action=>"result"
  end
  def show
    @search= Search.find params[:id]
    @users = @search.users(params[:page])
  end

end
